CREATE TYPE "user_status" AS ENUM('Pending', 'Actived', 'Freezed');
CREATE TYPE "user_role" AS ENUM('Member', 'Admin');
CREATE TABLE IF NOT EXISTS "users" ( -- 用户
    "id" CHAR(20) PRIMARY KEY,
    "email" VARCHAR(255) NOT NULL, -- 邮箱
    "password" VARCHAR(72) NOT NULL, -- 密码
    "status" user_status NOT NULL DEFAULT 'Pending', -- 状态
    "role" user_role NOT NULL DEFAULT 'Member', -- 角色
    "dateline" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE("email")
);
CREATE TABLE IF NOT EXISTS "urls" ( -- 链接
    "id" CHAR(20) PRIMARY KEY,
    "user_id" CHAR(20) NOT NULL, -- 用户ID
    "origin" VARCHAR NOT NULL, -- 原始
    "url" VARCHAR(6) NOT NULL, -- 短链接
    "hit" BIGINT NOT NULL DEFAULT 0, -- 访问次数
    "has_password" BOOLEAN NOT NULL DEFAULT FALSE, -- 是否有密码
    "password" VARCHAR(72) NOT NULL, -- 密码
    "has_expired" BOOLEAN NOT NULL DEFAULT FALSE, -- 是否有过期时间
    "expired" TIMESTAMPTZ NOT NULL DEFAULT '1970-01-01 00:00:00.0+8',
    "dateline" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE("url")
);
CREATE TABLE IF NOT EXISTS "url_logs" ( -- 链接访问日志
    "id" CHAR(20) PRIMARY KEY,
    "url" VARCHAR(6) NOT NULL, -- 短链接
    "ip" VARCHAR(39) NOT NULL DEFAULT '', -- IP
    "user_agent" VARCHAR NOT NULL DEFAULT '', -- 用户代理
    "dateline" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE VIEW  "v_user_urls"  AS 
SELECT u.email, r.id, r."user_id", r."origin",r."url",r."hit",r."has_password",r."password",r."has_expired",r."expired",r."dateline"
FROM users AS u
INNER JOIN urls AS r
ON u.id = r.user_id
;

CREATE VIEW "v_url_logs" AS
SELECT DISTINCT
    r.id AS url_id, r."user_id", r."origin",r."url",r."hit",r."has_password",r."password",r."has_expired",r."expired",r."dateline" AS url_dateline,
    g.id, g.ip, g.user_agent, g.dateline
FROM urls AS r
INNER JOIN url_logs AS g
ON r.url = g.url
;

CREATE VIEW "v_user_url_logs" AS
SELECT DISTINCT
    g."url_id", g."user_id", g."origin", g."url", g."hit", g."has_password", g."password", g."has_expired", g."expired", g."url_dateline", g."id", g."ip", g."user_agent", g."dateline",
    u.email
FROM users AS u
INNER JOIN v_url_logs AS g
ON g.user_id = u.id
;

-- 初始数据

INSERT INTO "users" ("id", "email", "password", "status", "role", "dateline") VALUES 
-- 管理员：admin@china.fr.mu, china.fr.mu
('cppp7fsdrfaqv0b9qoo0', 'admin@china.fr.mu', '$2b$12$3mRnXdyJotvzyrJwKdpW7OHJLa1XsRutCFRMIvUX40b505XI0nKQi', 'Actived', 'Admin', '2024-06-20 02:28:15.269606+00'),
-- 用户：user@china.fr.mu, china.fr.mu
('cppp90cdrfaqr4s389k0', 'user@china.fr.mu', '$2b$12$gt1h86aVUYAZtL8ZX9BlKef9dFsl.iwuOvbsHnt9rfr1tpZEhYsjS', 'Actived', 'Member', '2024-06-20 02:31:29.931893+00');
